package dao.impl;

import bean.User;
import bean.attendance;
import dao.attendanceDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.c3p0Utils;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class attendanceDaoImpl implements attendanceDao {
    private static QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
    @Override
    public int att_datetime(int uid, String date) {
        String sql = "insert into attendance (uid,datetime) values(?,?)";
        int i = 0;
        try {
            i = qr.update(sql,uid,date);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public int attendanceNum() {
        String sql="select COUNT(uid) attendanceNum FROM attendance where DAY(datetime)=?";
        Calendar cal = Calendar.getInstance();
        cal.setTimeInMillis( System.currentTimeMillis());
        attendance at=null;
        int day = cal.get(Calendar.DAY_OF_MONTH) ;
        try {
             at=qr.query(sql,new BeanHandler<attendance>(attendance.class),day);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return at.getAttendanceNum();
    }

    @Override
    public int NoattendanceNum() {
        String sql="select COUNT(uid) allnum FROM user";
        User u=null;
        int a=0;
        try {
            u=qr.query(sql,new BeanHandler<User>(User.class));
            a=attendanceNum();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return u.getAllnum()-a;
    }

    @Override
    public attendance Finduiddatetime(int uid,String date) {
        String sql="select * from attendance where uid=? AND datetime=?";
        attendance at= null;
        try {
            at = qr.query(sql,new BeanHandler<attendance>(attendance.class),uid,date);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return at;
    }

    @Override
    public List<attendance> attFind() {
        String sql="select * from attendance,user_info where uinfoid=uid ";
        List<attendance> list= null;
        try {
            list = qr.query(sql,new BeanListHandler<attendance>(attendance.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<attendance> rname_searchFind(String rname) {
        String sql="select * from attendance,user_info where uinfoid=uid AND rname LIKE ?";
        List<attendance> list= null;
        try {
            list = qr.query(sql,new BeanListHandler<attendance>(attendance.class),"%"+rname+"%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<attendance> datetime_searchFind(String datetime) {
        String sql="select * from attendance,user_info where uinfoid=uid AND datetime LIKE ?";
        List<attendance> list= null;
        try {
            list = qr.query(sql,new BeanListHandler<attendance>(attendance.class),"%"+datetime+"%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<attendance> userid_Find(int uid) {
        String sql="select DAY(datetime) day FROM attendance where MONTH(datetime)=? AND uid=?";
        List set=new ArrayList();
        Calendar cal = Calendar.getInstance();
        cal.setTimeInMillis( System.currentTimeMillis());
        int month = cal.get(Calendar.MONTH) + 1;
        List<attendance> list= null;
        try {
            list = qr.query(sql,new BeanListHandler<attendance>(attendance.class),month,uid);
            for (int i=0;i<list.size();i++){
                set.add(list.get(i).getDay());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /*
    * 根据接收的uid和月份，返回查询人某月的打卡记录
    * */
    @Override
    public List<attendance> rnaem_uid_Findday(int uid, String datetime) {
        String sql="select DAY(datetime) day FROM attendance where MONTH(datetime)=? AND uid=?";
        List set=new ArrayList();

        int month=Integer.parseInt(datetime);
        List<attendance> list= null;
        try {
            list = qr.query(sql,new BeanListHandler<attendance>(attendance.class),month,uid);
            for (int i=0;i<list.size();i++){
                set.add(list.get(i).getDay());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
